﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Drawing;
using System.Linq;
using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;
using ConfigLibrary;
using System.IO;
using System.Runtime.Serialization.Formatters.Binary;
using System.Windows.Forms;
using Workbench.Properties;

namespace Contacts
{

	public class MySqlConn
	{
		#region Globals
		//commands and adapters are placed at the top to use throughtout the class
		public DataSet dscontacts = new DataSet();
		private DataSet compareds = new DataSet();
		public DataTable original;
		public DataSet currdata = new DataSet();
		public MySqlConnection conn;
		MySqlCommand wmcmd = new MySqlCommand();
		//MySqlCommand compcmd = new MySqlCommand();
		MySqlCommand contcmd = new MySqlCommand();
		//MySqlCommand pcontcmd = new MySqlCommand();
		MySqlDataAdapter wmda = new MySqlDataAdapter();
		//MySqlDataAdapter daup = new MySqlDataAdapter();
		//MySqlDataAdapter compda = new MySqlDataAdapter();
		MySqlDataAdapter contda = new MySqlDataAdapter();
		//MySqlDataAdapter pcontda = new MySqlDataAdapter();
		DataRow updatedrow;
		DataRow uploadfixrow;
		DataSet contcomp = new DataSet();
		DataGridViewCell str = new DataGridViewTextBoxCell();
		bool ts;
		bool open = false;
		public string top;
		DataView pcview;
		string projecttype;
		DateTime today = DateTime.Today;
		ProgressBar pb2;
		Label lab_save;
		BackgroundWorker worker;
		// one sqlcommandbuilder per adapter
		// I hope this makes it easy to pick
		// which tables to update
		MySqlCommandBuilder _compbuilder;
		MySqlCommandBuilder _pcontbuilder;
		MySqlCommandBuilder _contbuilder;
		private static object synclock = new object();
		string ProjectSelection;
		protected const string traceout = "ContactsTrace";
		protected Microsoft.Exchange.WebServices.Authentication.TraceListener tracer;

		// Config info
		private Config config;
		private string _username;
		private string _host;
		private string _pass;
		private string _database;
		private int _port;
		private int rc;
		
		public DataSet _changed_data;
		public DataTable pc;
		private DataSet _original_data = new DataSet();
		private string _sql;
		private SqlBuilder builder;
		string[] db;
		#endregion

		#region CTOR
		/// <summary>
		/// Default constructor.  Initializes the config file
		/// </summary>
		public MySqlConn( )
		{
			//loads the config file
			try
			{
				
				//this switches between the mysql contacts and mysql test databases
				#if (DEBUG)

				Stream s = File.Open(Workbench.Properties.Settings.Default.CONFIGPATH, FileMode.Open);
				
				#elif (RELEASE)
				Stream s = File.Open(Workbench.Properties.Settings.Default.CONFIGPATH, FileMode.Open);
				#endif

				BinaryFormatter b = new BinaryFormatter();
				config = (Config)b.Deserialize(s);
				s.Close();
				config.GetSqlInfo(out _host, out _username, out _pass, out _database, out _port);
				db = _database.Split(new Char[] { ',' });
				tracer = new Microsoft.Exchange.WebServices.Authentication.TraceListener();
			}
			catch (Exception ex)
			{
				string subject = "MySqlConn Load Error";
				ErrorHandling.ErrorEmail(subject, ex);
			}
			//end load
		}

		/// <summary>
		/// Constructor for saving Data into Mysql
		/// </summary>
		/// <param name="Data">The System.Data.DataSet to save</param>
		public MySqlConn(DataSet Data, DataTable Table)
		{
			string table = Table.TableName;
			
			switch (table)
			{
				case "companies":
					{
						_compbuilder = new MySqlCommandBuilder(contda);
					}
					break;
				case "contacts":
					{
						_contbuilder = new MySqlCommandBuilder(contda);
					}
					break;
				case "project_contacts":
					{
						_pcontbuilder = new MySqlCommandBuilder(contda);
					}
					break;
			}
			
			// Firstly we need to split out the changed data from the dataset
			
			_original_data = Data;
			_changed_data = _original_data.GetChanges();
			//if (_changed_data.HasErrors) throw new InvalidDataException("Data has Errors.");
			builder = new SqlBuilder(_original_data);
			builder.BuilderCompleted += new SqlBuilder.SqlBuilderCompleted(builder_BuilderCompleted);
		}

		#endregion

		#region Events

		void builder_BuilderCompleted(string SqlStatement)
		{
			_sql += SqlStatement;
			StatmentCompleted(_sql);
		}

		// public event raised when the statement has been constructed
		public delegate void SaveStatementComplete(string SqlStatement);
		public event SaveStatementComplete StatmentCompleted;

		#endregion

		#region Public Properties

		public DataSet Data
		{ get { return dscontacts; } }

		#endregion

		#region Public Procedures

		/// <summary>
		/// Connects to the MySql server using the database, host, and logon credentials from the config file
		/// </summary>
		/// 
		
		public void Connect(string tablename)
		{
			// lock this code section
			lock (synclock) { tracer.Trace(traceout, "Connecting");
				//this establishes sql statements for the connection for the mysql contacts database
				//and the tables in the mysql contacts database
				try
				{ tracer.Trace(traceout, string.Format("Selecting Database: {0}",tablename));
					switch (tablename)
					{
						case "trident":
						case "spock":
							{
								conn = new MySqlConnection();
								conn.ConnectionString = "Database=test" + ";Data Source=" + _host + ";User Id=" + _username + ";Password=" + _pass + ";Port=3306" ;
								conn.Open();tracer.Trace(traceout, string.Format("Using Connection string: {0}",conn.ConnectionString));
							}
							break;
						case "contacts":
							{
								conn = new MySqlConnection();
								conn.ConnectionString = "Database=test" + ";Data Source=" + _host + ";User Id=" + _username + ";Password=" + _pass + ";Port=3306";
								conn.Open();tracer.Trace(traceout, string.Format("Using Connection string: {0}",conn.ConnectionString));
							}
							break;
					}
					GC.KeepAlive(conn);
				}
				catch (MySql.Data.MySqlClient.MySqlException ex)
				{
					string subject = "MySqlConn Connect Error";
					switch (ex.Number) {
						case 0:
							ErrorHandling.ErrorEmail(subject += " Cannot connect to server", ex);
							break;
						case 1045:
							ErrorHandling.ErrorEmail(subject += " Invalid username or password", ex);
							break;
					}
				}
			}
		}

		/// <summary>
		/// this establishes adapter for the mysql table catergories
		/// adds table and columns
		/// </summary>
		
		//this loads the the contacts database from mysql
		
		public void LoadWMdatabase (string tablename, ProjectTypes projecttype, StoreProgramTypes programtype, JobFilter jobs)
		{
			lock (synclock) {tracer.Trace(traceout, "Loading WM Database");
				// (select id, city from schedule.trident where proj_status = 'Active' and prog_yr_prj >= '2013' and prj_typ = 'NEW' or prj_typ = 'REL') union (select id, city from schedule.trident where proj_status = 'Active'and prj_typ = 'NEW' or prj_typ = 'REL' and go_prj >= '1-30-2013') order by city;
				// TODO: build a string of 'prj_typ' using |'s to inject into the command statement.
				string active = "Active";
				ProjectSelection = ProjectSQLStringBuilder (projecttype, jobs, tablename);tracer.Trace(traceout, string.Format("\tProject Filtering based on: {0}",ProjectSelection));
				DateTime offtrident = today.AddDays (-180);
				string removetrident = offtrident.ToString ("MM-dd-yyyy");
				string todaystr = today.ToString ("MM-dd-yyyy");
				int year = today.Year;
				int pastyear = year - 1;
				/*string command = "(Select id, str_num, str_seq, city, st from " + tablename + " where rundate = '" + today + "' AND proj_status = '" + active + "' AND prog_yr_prj >= '" + year +
					"'" + ProjectSelection + ") UNION (Select id, str_num, str_seq, city, st from " + tablename + " where rundate = '" + today + "' AND prog_yr_prj >= '" + pastyear + "' AND proj_status = '" + active +
					"' AND go_prj >= '" + removetrident + "'" + ProjectSelection + ") ORDER BY city";*/
				string command = "SELECT projects.* FROM projects JOIN schedule.trident ON (projects.str_num = trident.str_num and trident.str_seq = projects.str_seq) WHERE (trident.rundate LIKE DATE_FORMAT(CURDATE(), '%c/%e/%Y %h:%i:%s %p') AND (" + ProjectSelection + "));";
				wmcmd.CommandText = string.Format ("use {0}; {1}", /*"test"*/db [0], command);
				wmcmd.Connection = conn;tracer.Trace(traceout, string.Format("\tLoading Data using CommandText: {0}",wmcmd.CommandText));
				wmda.SelectCommand = wmcmd;
				wmda.TableMappings.Add (tablename, programtype.ToString());
				wmda.FillSchema(dscontacts, SchemaType.Source, tablename);
				wmda.MissingSchemaAction = MissingSchemaAction.AddWithKey;
				wmda.Fill(dscontacts, tablename);
				wmcmd.ExecuteNonQuery();tracer.Trace(traceout, string.Format("\tRows Loaded: {0}",dscontacts.Tables[programtype.ToString()].Rows.Count.ToString()));
				// rename columns if using the trident data directly
//				dscontacts.Tables[programtype.ToString()].Columns["id"].ColumnName = "str_id";
//				dscontacts.Tables[programtype.ToString()].Columns["city"].ColumnName = "str_city";
//				dscontacts.Tables[programtype.ToString()].Columns["st"].ColumnName = "str_st";
			}
		}
		
		public void LoadContacts()
		{tracer.Trace(traceout, "Loading All Contacts Tables");
			string[] commands = new string[] {"Select * from categories order by category","Select * from companies order by company_name",
				"Select * from contacts","Select * from job_roles order by job_role",string.Format("Select pc.* from project_contacts AS pc JOIN projects ON projects.str_id = pc.str_id JOIN schedule.trident ON (projects.str_num = trident.str_num and trident.str_seq = projects.str_seq) WHERE (trident.rundate LIKE DATE_FORMAT(CURDATE(), '%c/%e/%Y %h:%i:%s %p') AND ( {0} )) AND (pc.str_id = projects.str_id);", ProjectSelection )};
			string[] tables = new string[] { "categories", "companies", "contacts", "job_roles", "project_contacts" };
			int t = commands.Length;
			
			for (int i = 0; i < t; i++)
			{
				contcmd.CommandText = string.Format("use {0}; {1}", /*"test"*/db[0], commands[i]);
				//contcmd.CommandText = commands[i];
				contcmd.Connection = conn;tracer.Trace(traceout, string.Format("\tUsing CommandText: {0}",contcmd.CommandText));
				contda.SelectCommand = contcmd;
				contda.TableMappings.Add(tables[i], tables[i]);
				contda.FillSchema(dscontacts, SchemaType.Source, tables[i]);
				contda.MissingSchemaAction = MissingSchemaAction.AddWithKey;
				contda.Fill(dscontacts, tables[i]);
				lock (synclock)
				  contcmd.ExecuteNonQuery();tracer.Trace(traceout, string.Format("\tRows Loaded: {0}",dscontacts.Tables[tables[i]].Rows.Count.ToString()));
			}
			//trident is already loaded so use it 
			
		}
		
		public void LoadAllContacts()
		{tracer.Trace(traceout, "Loading All Contacts Tables");
			string[] commands = new string[] {"Select * from categories order by category","Select * from companies order by company_name",
				"Select * from contacts","Select * from job_roles order by job_role", /*"Select pc.* from project_contacts AS pc;"*/};
			string[] tables = new string[] { "categories", "companies", "contacts", "job_roles", /*"project_contacts"*/ };
			int t = commands.Length;
			
			for (int i = 0; i < t; i++)
			{
				contcmd.CommandText = string.Format("use {0}; {1}", /*"test"*/db[0], commands[i]);
				//contcmd.CommandText = commands[i];
				contcmd.Connection = conn;tracer.Trace(traceout, string.Format("\tUsing CommandText: {0}",contcmd.CommandText));
				contda.SelectCommand = contcmd;
				contda.TableMappings.Add(tables[i], tables[i]);
				contda.FillSchema(dscontacts, SchemaType.Source, tables[i]);
				contda.MissingSchemaAction = MissingSchemaAction.AddWithKey;
				contda.Fill(dscontacts, tables[i]);
				lock (synclock)
				  contcmd.ExecuteNonQuery();tracer.Trace(traceout, string.Format("\tRows Loaded: {0}",dscontacts.Tables[tables[i]].Rows.Count.ToString()));
			}
			//trident is already loaded so use it 
			
		}

		public void LoadTable(string tab, ref DataTable currtab)
		{tracer.Trace(traceout, string.Format("Loading Table: {0}", tab));
			contcmd.CommandText = string.Format("use {0}; Select * from " + tab + "", /*"test"*/db[0]);
			contcmd.Connection = conn; tracer.Trace(traceout, string.Format("\tUsing CommandText: {0}",contcmd.CommandText));
			contda.SelectCommand = contcmd;
			DataTableMapping map = new DataTableMapping();
			map.SourceTable = contda.TableMappings.ToString();tracer.Trace(traceout, string.Format("\tMap DataTable: {0}",map.SourceTable));
			if (map == null)
			{
			  contda.TableMappings.Add(tab, tab);tracer.Trace(traceout, string.Format("\t\tMapping is null, Add Mapping: {0}",tab));
			}
			contda.FillSchema(currdata, SchemaType.Source, tab);
			contda.MissingSchemaAction = MissingSchemaAction.AddWithKey;
			contda.Fill(currdata, tab);
			contcmd.ExecuteNonQuery();tracer.Trace(traceout, string.Format("\tRows Loaded: {0}",currdata.Tables[tab].Rows.Count.ToString()));
			currtab = currdata.Tables[tab];
		}

		public void LoadCompaniesComp(int comp_id, ref DataTable com)
		{tracer.Trace(traceout, "Loading Companies");
			contcmd.CommandText = string.Format("use {0}; Select * from companies where company_id =" + comp_id + "", /*"test"*/db[0]);
			contcmd.Connection = conn; tracer.Trace(traceout, string.Format("\tUsing CommandText: {0}",contcmd.CommandText));
			contda.SelectCommand = contcmd;
			contda.TableMappings.Add("Table", "companies");
			contda.FillSchema(contcomp, SchemaType.Source, "companies");
			contda.MissingSchemaAction = MissingSchemaAction.AddWithKey;
			contda.Fill(contcomp, "companies");
			contcmd.ExecuteNonQuery();tracer.Trace(traceout, string.Format("\tRows Loaded: {0}",contcomp.Tables["companies"].Rows.Count.ToString()));
			com = contcomp.Tables["companies"];
		}

		public void LoadContactsComp(int cont_id, ref DataTable contcurrent)
		{tracer.Trace(traceout, "Loading Contacts");
			contcmd.CommandText = string.Format("use {0}; Select * from contacts where contact_id =" + cont_id + "", /*"test"*/db[0]);
			contcmd.Connection = conn; tracer.Trace(traceout, string.Format("\tUsing CommandText: {0}",contcmd.CommandText));
			contda.SelectCommand = contcmd;
			contda.TableMappings.Add("Table", "contacts");
			contda.FillSchema(contcomp, SchemaType.Source, "contacts");
			contda.MissingSchemaAction = MissingSchemaAction.AddWithKey;
			contda.Fill(contcomp, "contacts");
			contcmd.ExecuteNonQuery();tracer.Trace(traceout, string.Format("\tRows Loaded: {0}",contcomp.Tables["contacts"].Rows.Count.ToString()));
			contcurrent = contcomp.Tables["contacts"];
			
		}

		public void LoadProjectContactsComp(ref DataTable pc)
		{tracer.Trace(traceout, "Loading Projects Contacts");
			//this establishes adapter for the mysql table project contacts
			//adds tables and columns
      tracer.Trace(traceout, string.Format("\tLoading Project Contacts Join Table: {0}",pc.TableName));
			contcmd.CommandText = string.Format("use {0}; Select pc.* from project_contacts AS pc JOIN projects ON projects.str_id = pc.str_id JOIN schedule.trident ON (projects.str_num = trident.str_num and trident.str_seq = projects.str_seq) WHERE (trident.rundate LIKE DATE_FORMAT(CURDATE(), '%c/%e/%Y %h:%i:%s %p') AND ( {1} )) AND (pc.str_id = projects.str_id);", /*"test"*/db[0], ProjectSelection );
			contcmd.Connection = conn;tracer.Trace(traceout, string.Format("\tUsing CommandText: {0}",contcmd.CommandText));
			contda.SelectCommand = contcmd;
			contda.TableMappings.Add("Table", "project_contacts");
			contda.FillSchema(contcomp, SchemaType.Source, "project_contacts");
			contda.MissingSchemaAction = MissingSchemaAction.AddWithKey;
			contda.Fill(contcomp, "project_contacts");
			contcmd.ExecuteNonQuery();tracer.Trace(traceout, string.Format("\tRows Loaded: {0}",contcomp.Tables["project_contacts"].Rows.Count.ToString()));
			pc = contcomp.Tables["project_contacts"];
			contcmd.Dispose();
			contda.Dispose();
		}

		//this deletes any selected row in the datagridview for deletion
		public void DeleteProjectContacts(DataGridView dgv, int pcid, int strid, ProjectTypes projecttype, ProgressBar pb, Label lab_save)
		{tracer.Trace(traceout, "Delete Row from Project Contacts");
			lock ( synclock) {
				
				//worker = bgwork;
				//pb2 = progbar;
				//lab_save = lab_sa;
				DataGridView datagv = new DataGridView();
				datagv = dgv;
				contcmd.CommandText = string.Format("use {0}; DELETE from project_contacts WHERE project_contact_id = '" + pcid + "'", /*"test"*/db[0]);
				//pcontcmd.CommandText = "DELETE from project_contacts WHERE project_contact_id = '" + pcid + "'";
				contcmd.Connection = conn; tracer.Trace(traceout, string.Format("\tUsing CommandText: {0}",contcmd.CommandText));
				contda.DeleteCommand = contcmd;
				contcmd.ExecuteNonQuery();tracer.Trace(traceout, string.Format("\tRow ID Deleted: {0}",pcid));
				func.FillFolder(dscontacts, datagv, strid, projecttype, pb, lab_save);
				func.FillSpreadsheet(dscontacts, datagv, strid, projecttype, pb, lab_save);
			}
		}

		/// <summary>
		/// Hard codes relationships between the data tables
		/// </summary>
		public void Relations(StoreProgramTypes programtype)
		{tracer.Trace(traceout, "Setting client-side data relations");
			//this establishes relationship between categories and companies

			DataColumn colparentcatcomp = dscontacts.Tables["categories"].Columns["category_id"];
			DataColumn colchildcatcomp = dscontacts.Tables["companies"].Columns["category_id"];
			DataRelation relcatcomp = new DataRelation("relcat2comp", colparentcatcomp, colchildcatcomp);
			dscontacts.Relations.Add(relcatcomp);tracer.Trace(traceout, string.Format("\tSet Relation: {0}",relcatcomp.RelationName));

			//this establishes relationship between companies and contacts

			DataColumn colparentcompcont = dscontacts.Tables["companies"].Columns["company_id"];
			DataColumn colchildcompcont = dscontacts.Tables["contacts"].Columns["company_id"];
			DataRelation relcompcont = new DataRelation("relcomp2cont", colparentcompcont, colchildcompcont);
			dscontacts.Relations.Add(relcompcont);tracer.Trace(traceout, string.Format("\tSet Relation: {0}",relcompcont.RelationName));


			//this establishes relationship between projects and project contacts

			DataColumn colparentprojpc = dscontacts.Tables[programtype.ToString()].Columns["str_id"];
			DataColumn colchildprojpc = dscontacts.Tables["project_contacts"].Columns["str_id"];
			DataRelation relprojpc = new DataRelation("relproj2pc", colparentprojpc, colchildprojpc);
			dscontacts.Relations.Add(relprojpc);tracer.Trace(traceout, string.Format("\tSet Relation: {0}",relprojpc.RelationName));

			//this establishes relationship between job roles and project contacts

			DataColumn colparentjrpc = dscontacts.Tables["job_roles"].Columns["job_role_id"];
			DataColumn colchildjrpc = dscontacts.Tables["project_contacts"].Columns["job_role_id"];
			DataRelation reljrpc = new DataRelation("reljr2pc", colparentjrpc, colchildjrpc);
			dscontacts.Relations.Add(reljrpc);tracer.Trace(traceout, string.Format("\tSet Relation: {0}",reljrpc.RelationName));

			//this establishes relationship between contacts and project contacts

			DataColumn colparentcontpc = dscontacts.Tables["contacts"].Columns["contact_id"];
			DataColumn colchildcontpc = dscontacts.Tables["project_contacts"].Columns["contact_id"];
			DataRelation relcontpc = new DataRelation("relcont2pc", colparentcontpc, colchildcontpc);
			dscontacts.Relations.Add(relcontpc);tracer.Trace(traceout, string.Format("\tSet Relation: {0}",relcontpc.RelationName));
		}

		public void Disconnect()
		{
			try
			{
				conn.Close();tracer.Trace(traceout, "Disconnect from Mysql Server");
			}
			catch
			{
        tracer.Trace(traceout, "Failed Disconnect from Mysql Server");
			}
		}

		/// <summary>
		/// Merge changed data into the original data. Only needed if the Data Sets have errors in them.
		/// </summary>
		public void MergeSets()
		{tracer.Trace(traceout, "Merging changed data into original data");
			foreach (DataTable table in _changed_data.Tables)
			{
				if (table.HasErrors)
				{
				  tracer.Trace(traceout, string.Format("\tTable has errors: {0}",table));
				  var errors = table.GetErrors();
				  foreach (DataRow row in errors) {
				    tracer.Trace(traceout, string.Format("\t\tFound error in row: {0}, message: {1}",row.ToString(), row.RowError));
				  }
					throw new InvalidDataException(string.Format("Error in {0}", table));
				}
			}
		}

		/// <summary>
		/// Call this after you've created your SQL connection object for saving
		/// </summary>
		public void MysqlPreSave(DataSet Data, DataTable Table, DataGridView datagrv, DataRow pulleddata, DataRow currentdata, int contactcompanyid, string timeofpull)
		{
			string table = Table.TableName;tracer.Trace(traceout, string.Format("\tSaving changes to table: {0}",table));
			_original_data = Data;
			top = timeofpull;tracer.Trace(traceout, string.Format("\t\tData pulled: {0}",top));
			if (table == "project_contacts")
			{
				if (Data.HasChanges(DataRowState.Added | DataRowState.Modified))
				{
					_changed_data = _original_data.GetChanges();
					pcview = new DataView(_original_data.Tables["project_contacts"]);
				}
			}
			
			//this is the workhorse to update mysql
			try
			{

				switch (table)
				{

					case "companies":
						{
							if (currentdata != null)
							{
								contactcompanycompare(pulleddata, currentdata, contactcompanyid);tracer.Trace(traceout, "\tcomparing Data");
								fixdata(pulleddata);
							}
							if (func.button == "b" || func.button == "c" || ts == true)
							{ updatedata(pulleddata); }
							_changed_data = _original_data.GetChanges();
							_compbuilder = new MySqlCommandBuilder(contda);
							//string.Format("use {0}; Select * from " + tab + "", db[0]);
							contcmd.CommandText = string.Format("use {0}; SELECT * FROM companies", db[0]); //this is the office command text
							//compcmd.CommandText = "SELECT * FROM companies"; //this is home command text
							contcmd.Connection = conn;tracer.Trace(traceout, string.Format("\tMysql Command: {0}",contcmd.CommandText));
							contda.SelectCommand = contcmd;
							contda.Update(_changed_data);tracer.Trace(traceout, "\t\tUpdating Mysql");
							_changed_data.AcceptChanges();tracer.Trace(traceout, "\t\tAccepting changes on local data");
							contcomp.Dispose();
						}
						break;

					case "contacts":
						{
							if (currentdata != null)
							{
								contactcompanycompare(pulleddata, currentdata, contactcompanyid);tracer.Trace(traceout, "\tcomparing Data");
								fixdata(pulleddata);
							}
							if (func.button == "b" || func.button == "c" || ts == true)
							{ updatedata(pulleddata); }
							_changed_data = _original_data.GetChanges();
							_contbuilder = new MySqlCommandBuilder(contda);
							contcmd.CommandText = string.Format("use {0}; SELECT * FROM contacts", db[0]); //this ios for the office
							//contcmd.CommandText = "SELECT * FROM contacts";  //this is for the home
							contcmd.Connection = conn;tracer.Trace(traceout, string.Format("\tMysql Command: {0}",contcmd.CommandText));
							contda.SelectCommand = contcmd;
							contda.Update(_changed_data);tracer.Trace(traceout, "\t\tUpdating Mysql");
							_changed_data.AcceptChanges();tracer.Trace(traceout, "\t\tAccepting changes on local data");
							contcomp.Dispose();
						}
						break;
					case "project_contacts":
						{
							_pcontbuilder = new MySqlCommandBuilder(contda);
							contcmd.CommandText = string.Format("use {0}; SELECT * FROM project_contacts", db[0]); //this is the office
							//pcontcmd.CommandText = "SELECT * FROM project_contacts"; //this is for home
							contcmd.Connection = conn;tracer.Trace(traceout, string.Format("\tMysql Command: {0}",contcmd.CommandText));
							contda.SelectCommand = contcmd;
							contda.Update(_changed_data, "project_contacts");tracer.Trace(traceout, "\t\tUpdating Mysql");
							Data.AcceptChanges();tracer.Trace(traceout, "\t\tAccepting changes on local data");
							contcomp.Dispose();
						}
						break;
				}

				if (datagrv != null)
				{
					func.FillRows(_original_data, datagrv, pb2, lab_save);
				}
			}
			catch (Exception ex)
			{
				string subject = "MySqlConn Update MySql Error";
				ErrorHandling.ErrorEmail(subject, ex);
			}
			
		}

		public void contactcompanycompare(DataRow pull, DataRow current, int pos)
		{
			try
			{
				if (pull != null && current != null)
				{
					bool version = current.HasVersion(DataRowVersion.Original);
					ts = String.Equals(pull["timestamp", DataRowVersion.Original].ToString(), current["timestamp"].ToString());
					if (ts == false)
					{
						compare compform = new compare();
						compform = new compare(pull, current, _original_data, top, _changed_data);
						compform.Load += new EventHandler(compform_Load);
						compform.ShowDialog();
					}
				}
			}
			catch (Exception ex)
			{
				string subject = "MySqlConn Load Compare Form Error";
				ErrorHandling.ErrorEmail(subject, ex);
			}
		}

		void compform_Load(object sender, EventArgs e)
		{
			//throw new NotImplementedException();
		}

		public void fixdata(DataRow row)
		{
			try
			{
				bool type = true;

				Type contcol5 = row.ItemArray[5].GetType();
				if (contcol5.Name != "Int32")
				{
					type = false;
				}


				switch (type)
				{
					case true:
						{
							if (func.fixdate != null)
							{
								updatedrow = func.fixdate;
								//string contid = updatedrow["contact_id"].ToString();
								//int uprow = int.Parse(contid);
								int uprow = (int)updatedrow["contact_id"];
								uploadfixrow = _original_data.Tables["contacts"].Rows.Find(uprow);
								uploadfixrow["first_name"] = updatedrow["first_name"].ToString();
								uploadfixrow["middle_name"] = updatedrow["middle_name"].ToString();
								uploadfixrow["last_name"] = updatedrow["last_name"].ToString();
								uploadfixrow["job_title"] = updatedrow["job_title"].ToString();
								uploadfixrow["company_id"] = updatedrow["company_id"].ToString();
								uploadfixrow["direct_phone"] = updatedrow["direct_phone"].ToString();
								uploadfixrow["mobile_phone"] = updatedrow["mobile_phone"].ToString();
								uploadfixrow["email"] = updatedrow["email"].ToString();
								uploadfixrow["timestamp"] = updatedrow["timestamp"].ToString();
								uploadfixrow["user"] = updatedrow["user"].ToString();
								uploadfixrow.AcceptChanges();
							}
						} break;
					case false:
						{
							if (func.fixdate != null)
							{
								updatedrow = func.fixdate;
								string contid = updatedrow["company_id"].ToString();
								int uprow = int.Parse(contid);
								uploadfixrow = _original_data.Tables["companies"].Rows.Find(uprow);
								uploadfixrow["company_name"] = updatedrow["company_name"].ToString();
								uploadfixrow["street"] = updatedrow["street"].ToString();
								uploadfixrow["suite"] = updatedrow["suite"].ToString();
								uploadfixrow["city"] = updatedrow["city"].ToString();
								uploadfixrow["state"] = updatedrow["state"].ToString();
								uploadfixrow["zip"] = updatedrow["zip"].ToString();
								uploadfixrow["main_phone"] = updatedrow["main_phone"].ToString();
								uploadfixrow["fax"] = updatedrow["fax"].ToString();
								uploadfixrow["category_id"] = updatedrow["category_id"];
								uploadfixrow["timestamp"] = updatedrow["timestamp"].ToString();
								uploadfixrow["user"] = updatedrow["user"].ToString();
								uploadfixrow.AcceptChanges();
							}
						} break;
				}
			}
			catch (Exception ex)
			{
				string subject = "MySqlConn fixdata Error";
				ErrorHandling.ErrorEmail(subject, ex);
			}
		}

		public void updatedata(DataRow row)
		{
			try
			{

				bool type = true;
				
				Type contcol5 = row.ItemArray[5].GetType();
				if (contcol5.Name != "Int32")
				{
					type = false;
				}
				


				int itemcount = row.ItemArray.Length;
				if (func.update != null || ts == true)
				{
					if (func.update != null)
					{
						updatedrow.Delete();
						updatedrow = func.update;
					}
					else if (ts == true)
					{
						updatedrow = row;
					}
					switch (type)
					{
						case true:
							{
								string contid = updatedrow["contact_id"].ToString();
								int uprow = int.Parse(contid);
								uploadfixrow = _original_data.Tables["contacts"].Rows.Find(uprow);
								uploadfixrow["first_name"] = updatedrow["first_name"].ToString();
								uploadfixrow["middle_name"] = updatedrow["middle_name"].ToString();
								uploadfixrow["last_name"] = updatedrow["last_name"].ToString();
								uploadfixrow["job_title"] = updatedrow["job_title"].ToString();
								uploadfixrow["company_id"] = updatedrow["company_id"].ToString();
								uploadfixrow["direct_phone"] = updatedrow["direct_phone"].ToString();
								uploadfixrow["mobile_phone"] = updatedrow["mobile_phone"].ToString();
								uploadfixrow["email"] = updatedrow["email"].ToString();
								uploadfixrow["timestamp"] = DateTime.Now;
								uploadfixrow["user"] = updatedrow["user"].ToString();
								if (String.IsNullOrEmpty(uploadfixrow["middle_name"].ToString()))
								{
									uploadfixrow["full_name"] = uploadfixrow["first_name"].ToString() + " " + uploadfixrow["last_name"].ToString();
								}
								else
								{
									uploadfixrow["full_name"] = uploadfixrow["first_name"].ToString() + " " + uploadfixrow["middle_name"].ToString() + " " + uploadfixrow["last_name"].ToString();
								}
							} break;
						case false:
							{
								string compid = updatedrow["company_id"].ToString();
								int uprow = int.Parse(compid);
								uploadfixrow = _original_data.Tables["companies"].Rows.Find(uprow);
								uploadfixrow["company_name"] = updatedrow["company_name"].ToString();
								uploadfixrow["street"] = updatedrow["street"].ToString();
								uploadfixrow["suite"] = updatedrow["suite"].ToString();
								uploadfixrow["city"] = updatedrow["city"].ToString();
								uploadfixrow["state"] = updatedrow["state"].ToString();
								uploadfixrow["zip"] = updatedrow["zip"].ToString();
								uploadfixrow["main_phone"] = updatedrow["main_phone"].ToString();
								uploadfixrow["fax"] = updatedrow["fax"].ToString();
								uploadfixrow["category_id"] = updatedrow["category_id"].ToString();
								uploadfixrow["timestamp"] = updatedrow["timestamp"].ToString();
								uploadfixrow["user"] = updatedrow["user"].ToString();
							} break;
					}
				}
			}
			catch (Exception ex)
			{
				string subject = "MySqlConn updatedata Error";
				ErrorHandling.ErrorEmail(subject, ex);
			}
		}


		#endregion
		protected string ProjectSQLStringBuilder(ProjectTypes projects, JobFilter jobs, string tablename)
		{
			// TODO: build a string of 'prj_typ' using |'s to inject into the command statement.
			// Returns a string must be "AND prj_typ = <type> OR prj_typ = <type> OR ..."
			//Wal-Mart types are: EXP, NEW, REL, RMC, T/O, OSR, LND-NEW, LND-T/O
			List<string> filter = new List<string>();
			var typevalues = Enum.GetValues(typeof(ProjectTypes));
			foreach (ProjectTypes value in typevalues) {
				if ((projects & value) == value) {
					switch (value) {
						#region cases
						case ProjectTypes.Expansion:
							filter.Add(tablename + ".prj_typ = 'EXP'");
							break;
						case ProjectTypes.Fixturing:
							filter.Add(tablename + ".prj_typ = 'FIX'");
							break;
						case ProjectTypes.GroundUp:
							filter.Add(tablename + ".prj_typ = 'NEW'");
							break;
						case ProjectTypes.Remodel:
							filter.Add(tablename + ".prj_typ = 'REM'");
							break;
						case ProjectTypes.RemodelC:
							filter.Add(tablename + ".prj_typ = 'RMC'");
							break;
						case ProjectTypes.Special:
							filter.Add(tablename + ".prj_typ = 'SPC'");
							break;
						case ProjectTypes.TakeOver:
							filter.Add(tablename + ".prj_typ = 'T/O'");
							break;
						case ProjectTypes.Tenant:
							filter.Add(tablename + ".prj_typ = 'TEN'");
							break;
						case ProjectTypes.LandBank:
							filter.Add(tablename + ".prj_typ = 'LND-NEW'");
							filter.Add(tablename + ".prj_typ = 'LND-T/O'");
							break;
						case ProjectTypes.OnSiteRelo:
							filter.Add(tablename + ".prj_typ = 'OSR'");
							break;
						case ProjectTypes.Relo:
							filter.Add(tablename + ".prj_typ = 'REL'");
							break;
							#endregion
					}
				}
			}
			string result = string.Empty;
			#if DEBUG
			filter.Sort();
			#endif
			if (filter.Count == 1) {
				result = " " + filter [0];
			} else {
				//result = "AND ";
				foreach (string storecommand in filter) {
					result += storecommand + " OR ";
				}
				result = result.Remove(result.Length - 4, 4);
			}
			if (jobs == JobFilter.Current) result += ") AND ((IF(schedule.trident.go_act = '',DATE(STR_TO_DATE(schedule.trident.go_prj, '%m-%d-%Y')),DATE(STR_TO_DATE(schedule.trident.go_act, '%m-%d-%Y'))) >= CURDATE()) ";
			return result; 
		}
		
		private void mail(string title, Exception ex)
		{
			Mail.mailman.sendmail(string.Format("MySql connection failed to load {0}", title), string.Format("Method call: {0} \r\nError Message: {1} \r\nCall Stack: {2}", ex.TargetSite, ex.Message, ex.StackTrace));
		}
	}

	#region MySqlException Class
	/// <summary>
	/// Class for returning MySql errors.
	/// </summary>
	public class MySqlException : SystemException
	{
		private string _mess;
		//private MySqlErrorCode _err;
		private Exception _inner;

		/// <summary>
		/// Gets or Sets your return message
		/// </summary>
		public string Message
		{
			get { return _mess; }
			set { _mess = value; }
		}

		/// <summary>
		/// Gets or Sets the Mysql errorcode
		/// </summary>
		//public MySqlErrorCode ErrorCode
		//{
		//    get { return _err; }
		//    set { _err = value; }
		//}

		/// <summary>
		/// Retutns the contianed exception, if any.
		/// </summary>
		public Exception innerException
		{
			get { return _inner; }
		}

		public MySqlException()
		{
		}
		public MySqlException(string Message)
		{
			_mess = Message;
		}
		public MySqlException(string Message, Exception innerException)
		{
			_mess = Message;
			_inner = innerException;
		}
	}
	#endregion

	#region SqlBuilder Class
	public class SqlBuilder
	{

		private DataSet _data;
		private string _sqlstatement;
		private int threads;

		// public event raised when the statement has been constructed
		public delegate void SqlBuilderCompleted(string SqlStatement);
		public event SqlBuilderCompleted BuilderCompleted;

		public SqlBuilder(DataSet DataWithChanges)
		{
			_data = DataWithChanges;
			GC.KeepAlive(_data);
		}

		public SqlBuilder(DataSet DataWithChanges, int Threads)
		{
			_data = DataWithChanges;
			GC.KeepAlive(_data);
			threads = Threads - 1;
		}

		public void Start()
		{
			System.Threading.ManualResetEvent[] threadsdone = new System.Threading.ManualResetEvent[threads];
			SqlWorker[] workers = new SqlWorker[threads];
			SqlWorker worker = null;
			for (int i = 0; i < threads; i++)
			{
				threadsdone[i] = new System.Threading.ManualResetEvent(false);
				switch (i)
				{
					case 0:
						if (_data.HasChanges(DataRowState.Added))
						{
							worker = new SqlWorker(_data.GetChanges(DataRowState.Added), DataRowState.Added, threadsdone[i]);
						}
						break;
					case 1:
						if (_data.HasChanges(DataRowState.Deleted))
						{
							worker = new SqlWorker(_data.GetChanges(DataRowState.Deleted), DataRowState.Deleted, threadsdone[i]);
						}
						break;
						// invalid enumerator, don't know why
						//case 2:
						//    worker = new SqlWorker(_data.GetChanges(DataRowState.Detached), DataRowState.Detached, threadsdone[i]);
						//    break;
					case 2:
						if (_data.HasChanges(DataRowState.Modified))
						{
							worker = new SqlWorker(_data.GetChanges(DataRowState.Modified), DataRowState.Modified, threadsdone[i]);
						}
						break;
					case 3:
						if (_data.HasChanges(DataRowState.Unchanged))
						{
							worker = new SqlWorker(_data.GetChanges(DataRowState.Unchanged), DataRowState.Unchanged, threadsdone[i]);
						}
						break;
				}

				workers[i] = worker;
				System.Threading.ThreadPool.QueueUserWorkItem(worker.SqlWorkCallback, i);
			}

			// wait until all thread complete
			System.Threading.WaitHandle.WaitAll(threadsdone);
			for (int i = 0; i < threads; i++)
			{
				worker = workers[i];
				_sqlstatement += worker.SqlStatment;

				//raise event
			}
			BuilderCompleted(_sqlstatement);
			_data.Dispose();
		}

		public void StartSave(object[] CommandBuilders)
		{
			threads = CommandBuilders.GetUpperBound(0);
			System.Threading.ManualResetEvent[] threadsdone = new System.Threading.ManualResetEvent[threads];
			SqlWorker[] workers = new SqlWorker[threads];
			SqlWorker worker = null;

			for (int i = 0; i < threads; i++)
			{
				threadsdone[i] = new System.Threading.ManualResetEvent(false);

				worker = new SqlWorker(CommandBuilders[i], _data, threadsdone[i]);

				workers[i] = worker;
				System.Threading.ThreadPool.QueueUserWorkItem(worker.SqlSaveCallback, i);
			}

			// wait until all thread complete
			System.Threading.WaitHandle.WaitAll(threadsdone);
			for (int i = 0; i < threads; i++)
			{
				worker = workers[i];
				_sqlstatement += worker.SqlStatment;

				//raise event
			}
			BuilderCompleted(_sqlstatement);
			_data.Dispose();
		}
	}

	public class SqlWorker
	{
		private DataSet _data;
		private DataTable _table;
		private string _sqlstatement;
		private System.Threading.ManualResetEvent _done;
		private DataRowState _state;
		private Config config;
		private MySqlCommandBuilder _combuild;

		public string SqlStatment { get { return _sqlstatement; } }

		/// <summary>
		/// Default constructor, provide the only the Changed DataSet and the DataRowState used to get those changes
		/// </summary>
		/// <param name="DataChanged"></param>
		public SqlWorker(DataSet DataChanged, DataRowState State, System.Threading.ManualResetEvent DoneEvent)
		{
			_data = DataChanged;
			_done = DoneEvent;
			_state = State;
		}

		public SqlWorker(DataTable DataTableChanged, System.Threading.ManualResetEvent DoneEvent)
		{
			_table = DataTableChanged;
			_done = DoneEvent;
			Stream s = File.Open(Workbench.Properties.Settings.Default.CONFIGPATH, FileMode.Open);
			BinaryFormatter b = new BinaryFormatter();
			config = (Config)b.Deserialize(s);
			s.Close();
		}

		public SqlWorker(object a, DataSet Data, System.Threading.ManualResetEvent DoneEvent)
		{
			_combuild = (MySqlCommandBuilder)a;
			_done = DoneEvent;
			_data = Data;
		}

		public void SqlWorkCallback(Object context)
		{
			// int threadIndex = (int)context;
			// log "thread processing {1}", threadIndex;
			_sqlstatement = ChangeStates();
			_done.Set();
			// MessageBox.Show(_sqlstatement);
		}

		public void SqlSaveCallback(Object context)
		{
			// int threadIndex = (int)context;
			// log "thread processing {1}", threadIndex;
			MySqlDataAdapter adapter = _combuild.DataAdapter;
			//MySqlCommand com = adapter.SelectCommand;
			//MySqlConnection connector = com.Connection;
			
			//connector.Open();
			

			////MySqlCommand command = new MySqlCommand(sql, connector);
			////command.CommandText = string.Format("SELECT {0} FROM contacts", ContactName);

			_combuild.GetUpdateCommand();
			_combuild.GetInsertCommand();
			_combuild.GetDeleteCommand();
			
			//adapter.UpdateCommand = command;
			// need an InsertCommand for new rows
			adapter.Update(_data);

			//connector.Close();
			_done.Set();
			MessageBox.Show("Saved, I think.");
		}

		private string ChangeStates()
		{
			string statement = "";
			foreach (DataTable table in _data.Tables)
			{
				statement += Builder(table);
			}
			return statement;
		}

		private string Builder(DataTable table)
		{
			string statement = "";
			switch (_state)
			{
				case DataRowState.Added:
					for (int i = 0; i < table.Rows.Count; i++)
					{
						// TODO:  make real SQL statements
						foreach (DataRow row in table.Rows)
						{
							string values = "";
							foreach (object o in row.ItemArray)
							{
								values += string.Format("{0}, ", o);
							}
							statement += string.Format("INSERT {0} VALUES {1};", table.TableName, values);
						}
					}
					return statement;
				case DataRowState.Deleted:
					for (int i = 0; i < table.Rows.Count; i++)
					{
						// TODO:  make real SQL statements
						statement += string.Format("DELETE {0} WHERE {1};", table.TableName, table.Rows[i].ItemArray[0].ToString());
					}
					return statement;
				case DataRowState.Detached:
					return statement;
				case DataRowState.Modified:
					for (int i = 0; i < table.Rows.Count; i++)
					{
						// TODO:  make real SQL statements
						foreach (DataRow row in table.Rows)
						{
							string values = "";
							foreach (object o in row.ItemArray)
							{
								values += string.Format("{0}, ", o);
							}
							statement += string.Format("REPLACE {0} VALUES {1};", table.TableName, values);
						}
					}
					return statement;
				case DataRowState.Unchanged:
					return statement;
				default:
					return statement;
			}
		}
	}
	#endregion
}